package com.allinpay;

import com.allinpay.model.Field;
import com.allinpay.model.Table;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.ArrayList;
import java.util.Vector;

/**
 * @author : wushikai
 * <p>
 *     使用之前必须满足1范式, 否则报异常
 * date : 2020-06-17
 */
public class Excel2mysql {

    private static final String FILE_PATH = "D:\\java\\codeLibraries\\tools\\mysqlTools\\target\\classes\\temp.xls";

    public static FileInputStream fileInputStream = null;
    public static BufferedInputStream bufferedInputStream = null;
    public static Integer lastRowIndex = -1;
    public static HSSFWorkbook workbook = null;
    public static HSSFSheet sheet = null;

    static {

        {
            try {
                fileInputStream = new FileInputStream(FILE_PATH);
                bufferedInputStream = new BufferedInputStream(fileInputStream);


                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(bufferedInputStream);

                workbook = new HSSFWorkbook(poifsFileSystem);

                sheet = workbook.getSheet("Sheet1");

                lastRowIndex = sheet.getLastRowNum();
            } catch (IOException e) {
                e.printStackTrace();

            }
        }


    }

    /**
     * 
     * 调用 sheet.getLastRowNum() ;可是这样有时候会出现一些问题,例如,当其中一行的数据的确都为空,可是其原本的格式还在,
     * 并没有连带删除,这样计算出来的行数就不真实（比真实的大）,还有当出现空白行时（也即某一行没有任何数据,
     * 通过Row row = sheet.getRow(i) 返回的row值为null),计算出来的值也不正确
     * 对execel表进行过滤,将那些没有意义的行删掉,之后再调用sheet.getLastRowNum() 得到的值就是正确的了
     * 
     * */
    public void getFlushRealRow(){
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream("你自己的.xls"));
        } catch (Exception e) {
            //
        }
        Sheet sheet = wb.getSheetAt(0);
        CellReference cellReference = new CellReference("A4");
        boolean flag = false;
        System.out.println("总行数："+(sheet.getLastRowNum()+1));
        for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
            Row r = sheet.getRow(i);
            if(r == null){
                // 如果是空行（即没有任何数据、格式）,直接把它以下的数据往上移动
                sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
                continue;
            }
            flag = false;
            for(Cell c:r){
                if(c.getCellType() != Cell.CELL_TYPE_BLANK){
                    flag = true;
                    break;
                }
            }
            if(flag){
                i++;
                continue;
            }
            else{//如果是空白行（即可能没有数据,但是有一定格式）
                if(i == sheet.getLastRowNum())//如果到了最后一行,直接将那一行remove掉
                    sheet.removeRow(r);
                else//如果还没到最后一行,则数据往上移一行
                    sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
            }
        }
        System.out.println("总行数："+(sheet.getLastRowNum()+1));
    }



    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                    // return new Result(true,firstRow+1,lastRow+1,firstColumn+1,lastColumn+1);
                }
            }
        }
        return false;
        //return new Result(false,0,0,0,0);
    }


    public static void readExcel() throws IOException {

        //System.out.println("在excel中的最后一行是:" + lastRowIndex + 1);

        for (int i = 0; i <= lastRowIndex; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                break;
            }

            short lastCellNum = row.getLastCellNum();

            // System.out.println("lastCellNum = " + lastCellNum);
            //System.out.println("是否合并单元格" + isMergedRegion(sheet, i, lastCellNum));
            //System.out.println("row.getCell(3).getColumnIndex() = " + row.getCell(2).getColumnIndex());


            for (int j = 0; j < lastCellNum; j++) {
                String cellValue = row.getCell(j).getStringCellValue();
                System.out.print(cellValue + "   ");
            }
            System.out.println();
        }


        bufferedInputStream.close();
    }





    public static Vector<String> getTableNames() {
        Vector<String> tableNames = new Vector<>();



        //统计整张表中的合并单元格个数
        int sheetMergeCount = sheet.getNumMergedRegions();



        for (int i = 0; i < sheetMergeCount; i++) {

            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
            int firstColumn = cellRangeAddress.getFirstColumn();
            int firstRow = cellRangeAddress.getFirstRow();
            System.out.println("excel中的坐标: (firstRow, firstColumn) = " + (firstColumn+1) +"  , "+ (firstRow +1));

            HSSFRow row = sheet.getRow(firstRow);
            String cellValue = row.getCell(firstColumn).getStringCellValue();
            tableNames.add(cellValue);


            // System.out.println(cellValue);
        }

        return tableNames;
    }

    /**
     * if只有一个表, 找到一个表名之后, 除了表名下的第二行, 剩下的全是字段
     * 判空
     * @return
     */
    public static Table getFirstTable() {

        CellRangeAddress cellRangeAddress = sheet.getMergedRegion(0);
        int firstColumn = cellRangeAddress.getFirstColumn();
        int firstRow = cellRangeAddress.getFirstRow();
       // System.out.println("excel中的坐标: (firstRow, firstColumn) = " + (firstColumn+1) +"  , "+ (firstRow +1));

        HSSFRow row = sheet.getRow(firstRow);
        String tableName = row.getCell(firstColumn).getStringCellValue();
        String tableComment=row.getCell(firstColumn-1).getStringCellValue();
        Table table = new Table();
        table.setTableName(tableName);
        table.setTableComment(tableComment);
        CellRangeAddress cellRangeAddressNext = sheet.getMergedRegion(1);
        int firstRowNext = 0;
        if (cellRangeAddressNext!=null){

             firstRowNext = cellRangeAddressNext.getFirstRow();
        }else{
            firstRowNext=lastRowIndex;
        }

        ArrayList<Field> fields=new ArrayList<>();
        //+2是因为指针往下指一个格子, 并且第一行是说明, 不能添加进去, so , 再次加一行==> +2
        for (int i = firstRow+2; i < firstRowNext; i++) {

            HSSFRow fieldRow=sheet.getRow(i);
            //直接在filed类中创建一个构造器, 两行解决
            String fieldName = fieldRow.getCell(0).getStringCellValue();
            String fieldType = fieldRow.getCell(1).getStringCellValue();
            String fieldComment = fieldRow.getCell(2).getStringCellValue();
            Field field = new Field();
            field.setFieldName(fieldName);
            field.setFieldType(fieldType);
            field.setFieldComment(fieldComment);
            System.out.println("field = " + field);
            fields.add(field);

        }

        table.setFields(fields);

        //添加属性字段

        return table;

    }


    /**
     * 把所有的表都读出来, 封装到一个泛型为Table 的list中
     * @return ArrayList<Table>
     */
    public static ArrayList<Table> getAllTable() {

        ArrayList<Table> tables=new ArrayList<>();
        //获得合并的列 有多少个 , 一般合并的列都是 表名
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);


            //获取合并单元格对象的第一行和第一列==> 坐标
            int rowPosition = cellRangeAddress.getFirstRow();
            int columnPosition = cellRangeAddress.getFirstColumn();

            //根据坐标去读取 excel 行对象, 把合并单元格的行读出来
            HSSFRow row = sheet.getRow(rowPosition);

            //行对象根据列坐标去获取 表名和表注释;
            String tableName = row.getCell(columnPosition).getStringCellValue();
            String tableComment=row.getCell(columnPosition-1).getStringCellValue();

            //封装对象
            Table table = new Table();
            table.setTableName(tableName);
            table.setTableComment(tableComment);

            //读取表属性 , 表属性在 两个表数据之间, 指针指向下一个表数据, 获取坐标
            CellRangeAddress cellRangeAddressNext = sheet.getMergedRegion(i+1);

            if (cellRangeAddressNext == null){
                break;
            }

            int firstRowNext = cellRangeAddressNext.getFirstRow();
            ArrayList<Field> fields=new ArrayList<>();
            //+2是因为指针往下指一个格子, 并且第一行是说明, 不能添加进去, so , 再次加一行==> +2
            for (int j = rowPosition+2; j < firstRowNext; j++) {

                HSSFRow fieldRow=sheet.getRow(j);
                //直接在filed类中创建一个构造器, 两行解决, 封装数据
                String fieldName = fieldRow.getCell(0).getStringCellValue();
                String fieldType = fieldRow.getCell(1).getStringCellValue();
                String fieldComment = fieldRow.getCell(2).getStringCellValue();
                Field field = new Field();
                field.setFieldName(fieldName);
                field.setFieldType(fieldType);
                field.setFieldComment(fieldComment);
                //System.out.println("field = " + field);
                fields.add(field);

            }

            table.setFields(fields);
            tables.add(table);
        }

        return tables;

    }

    public static void generateSqlFile() throws IOException {
        File sqlFile=new File("createTable.sql");

        String sqlStr=getAllTableSql();

        int len;
        byte[] bytes=sqlStr.getBytes();

        FileOutputStream fileOutputStream=new FileOutputStream(sqlFile, true);

        fileOutputStream.write(bytes);

        fileOutputStream.close();

    }


    public static void write2Mysql() {


    }


    public  static void getFirstTableSql ( ){
        Table firstTable = getFirstTable();
        ArrayList<Field> fields = firstTable.getFields();

        String sql="DROP TABLE IF EXISTS `"+firstTable.getTableName()+"`; \n CREATE TABLE `"+firstTable.getTableName()+"` (";
        StringBuilder stringBuilder=new StringBuilder( sql);

        for (Field field: fields){
            if ("id".equals(field.getFieldName())){

                stringBuilder.append( "`"+field.getFieldName().trim()+"`  "+field.getFieldType()+"  "+  "NOT NULL" ) ;
            }else
            {
                stringBuilder.append(" `"+field.getFieldName().trim()+"` "+field.getFieldType()+"  DEFAULT NULL COMMENT '"+field.getFieldComment()+"'");
            }
            stringBuilder.append(",");

        }

        //stringBuilder.delete(stringBuilder.length()-1, stringBuilder.length());
        stringBuilder.append("PRIMARY KEY (`id`)");
        stringBuilder.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='"+firstTable.getTableComment()+"';");
        System.out.println(stringBuilder.toString());
    }


    //等待后续优化
    public static String getAllTableSql(){


        ArrayList<Table> tables = Excel2mysql.getAllTable();
        StringBuilder allTableSql=new StringBuilder();
        StringBuilder oneTableSql=null;
        for (Table table: tables){
            ArrayList<Field> fields = table.getFields();

            String sql="DROP TABLE IF EXISTS `"+table.getTableName()+"`; \n CREATE TABLE `"+table.getTableName()+"` (";
            oneTableSql =new StringBuilder( sql);

            for (Field field: fields){
                if ("id".equals(field.getFieldName())){

                    oneTableSql.append( "`"+field.getFieldName()+"`  "+field.getFieldType()+"   "+  "NOT NULL" ) ;
                }else
                {
                    oneTableSql.append(" `"+field.getFieldName()+"` "+field.getFieldType()+"  DEFAULT NULL COMMENT '"+field.getFieldComment()+"'");
                }
                oneTableSql.append(", \n");

            }

            //stringBuilder.delete(stringBuilder.length()-1, stringBuilder.length());
            oneTableSql.append("PRIMARY KEY (`id`)");
            oneTableSql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='"+table.getTableComment()+"';");
            oneTableSql.append("\n");
            oneTableSql.append("\n");
            allTableSql.append(oneTableSql.toString());
        }

        System.out.println("allTableSql.toString() = " + allTableSql.toString());
        return allTableSql.toString();

    }



    public static int getTableNums() {
        //获取sheet中有多少个合并单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        System.out.println("sheetMergeCount = " + sheetMergeCount);
        return sheetMergeCount;
    }


    public static void main(String[] args){
        getFirstTable();
        getFirstTableSql();
     /* getAllTable();
      getAllTableSql();*/
    }


}



      /*
         FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();  target/classes/excel2mysql.xlsx
       FileInputStream fileInputStream = null;

        {
            try {
                fileInputStream = new FileInputStream(FILE_PATH);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
                return;
            }
        }

        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);


        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(bufferedInputStream);

        HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);

        HSSFSheet sheet = workbook.getSheet("Sheet1");

        int lastRowIndex = sheet.getLastRowNum();
        */